『Data Uploader』で実践！データアップロードジョブ(MySQLのデータを抽出→変換処理→Amazon S3にアップロード) #データ統合基盤 #CSアナリティクス
当エントリは『クラスメソッド CSアナリティクス Advent Calendar 2020』13日目のエントリです。
- クラスメソッド CSアナリティクス Advent Calendar 2020 - Qiita
- クラスメソッド CSアナリティクス Advent Calendar 2020 ｜ 特集カテゴリー ｜ Developers.IO
- ビッグデータ分析支援のカスタマーストーリーアナリティクス｜クラスメソッド｜クラスメソッドのサービス
当エントリでは、クラスメソッドが展開しているデータ統合基盤サービス『CSアナリティクス』(以降"CSA")のプロダクト群を用いて『任意のデータソースからデータを抽出し、変換処理を経た上で任意のクラウドストレージにデータをアップロードする』一連の流れを作り上げる流れについて説明します。
目次
「データアップロードジョブ」とは
CSA Data Uploaderでは、作成出来る「ジョブの定義」に幾つかパターンが存在します。当エントリで紹介するのはその中の「データアップロード」というジョブです。
このジョブは『任意の場所からデータを抽出して、簡易的な変換処理を踏まえた後でクラウドストレージにファイルをアップロード』するというケースを実現するためのジョブタイプです。ジョブ作成時に『入力』『変換』『出力』の処理パートが用意され、それぞれのパートで必要な処理を画面UI経由で設定します。
当エントリで実践する内容
当エントリでは、以下の処理を経てデータをアップロードする流れをCSA Data Uploaderで実践してみます。
- 入力：オンプレ想定のMySQLからODBC接続を介してデータを抽出
- 変換：抽出したデータに対し、「必要な項目の選択」と「項目順序の並び替え」を行う
- 出力：抽出・変換したデータをAmazon S3に任意の形式でファイルとしてアップロード
CSA Data Uploader データアップロード実践
入力処理の作成:MySQLからデータを抽出
まずはファーストステップ、MySQLからデータを抽出するパートの実践です。検証用に、郵便番号情報を下記の形でMySQLデータベースに格納しておきました。
$ mysql -h xxxxxxxxxxxxx --local-infile=1 -u xxxxxxx -p
mysql> use csadudb;
mysql> LOAD DATA LOCAL INFILE './ken_all-utf8.csv' \
-> INTO TABLE csadudb.zipcode \
-> FIELDS TERMINATED BY ',' \
-> OPTIONALLY ENCLOSED BY '"' \
-> LINES TERMINATED BY '\r\n';
Query OK, 124508 rows affected (3.90 sec)
Records: 124508 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM csadudb.zipcode;
+----------+
| COUNT(*) |
+----------+
| 124508 |
+----------+
1 row in set (0.18 sec)
mysql> SELECT * FROM csadudb.zipcode LIMIT 20;
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
| jiscode | zipcode_old | zipcode | pref_kana | city_kana | street_kana | pref | city | street | flag1 | flag2 | flag3 | flag4 | flag5 | flag6 |
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
| 01101 | 060 | 0600000 | ホッカイドウ | サッポロシチュウオウク | イカニケイサイガナイバアイ | 北海道 | 札幌市中央区 | 以下に掲載がない場合 | 0 | 0 | 0 | 0 | 0 | 0 |
| 01101 | 064 | 0640941 | ホッカイドウ | サッポロシチュウオウク | アサヒガオカ | 北海道 | 札幌市中央区 | 旭ケ丘 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600041 | ホッカイドウ | サッポロシチュウオウク | オオドオリヒガシ | 北海道 | 札幌市中央区 | 大通東 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600042 | ホッカイドウ | サッポロシチュウオウク | オオドオリニシ(1-19チョウメ) | 北海道 | 札幌市中央区 | 大通西（１〜１９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640820 | ホッカイドウ | サッポロシチュウオウク | オオドオリニシ(20-28チョウメ) | 北海道 | 札幌市中央区 | 大通西（２０〜２８丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600031 | ホッカイドウ | サッポロシチュウオウク | キタ1ジョウヒガシ | 北海道 | 札幌市中央区 | 北一条東 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600001 | ホッカイドウ | サッポロシチュウオウク | キタ1ジョウニシ(1-19チョウメ) | 北海道 | 札幌市中央区 | 北一条西（１〜１９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640821 | ホッカイドウ | サッポロシチュウオウク | キタ1ジョウニシ(20-28チョウメ) | 北海道 | 札幌市中央区 | 北一条西（２０〜２８丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600032 | ホッカイドウ | サッポロシチュウオウク | キタ2ジョウヒガシ | 北海道 | 札幌市中央区 | 北二条東 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600002 | ホッカイドウ | サッポロシチュウオウク | キタ2ジョウニシ(1-19チョウメ) | 北海道 | 札幌市中央区 | 北二条西（１〜１９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640822 | ホッカイドウ | サッポロシチュウオウク | キタ2ジョウニシ(20-28チョウメ) | 北海道 | 札幌市中央区 | 北二条西（２０〜２８丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600033 | ホッカイドウ | サッポロシチュウオウク | キタ3ジョウヒガシ | 北海道 | 札幌市中央区 | 北三条東 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600003 | ホッカイドウ | サッポロシチュウオウク | キタ3ジョウニシ(1-19チョウメ) | 北海道 | 札幌市中央区 | 北三条西（１〜１９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640823 | ホッカイドウ | サッポロシチュウオウク | キタ3ジョウニシ(20-30チョウメ) | 北海道 | 札幌市中央区 | 北三条西（２０〜３０丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600034 | ホッカイドウ | サッポロシチュウオウク | キタ4ジョウヒガシ(1-8チョウメ) | 北海道 | 札幌市中央区 | 北四条東（１〜８丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600004 | ホッカイドウ | サッポロシチュウオウク | キタ4ジョウニシ(1-19チョウメ) | 北海道 | 札幌市中央区 | 北四条西（１〜１９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640824 | ホッカイドウ | サッポロシチュウオウク | キタ4ジョウニシ(20-30チョウメ) | 北海道 | 札幌市中央区 | 北四条西（２０〜３０丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600035 | ホッカイドウ | サッポロシチュウオウク | キタ5ジョウヒガシ | 北海道 | 札幌市中央区 | 北五条東 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 060 | 0600005 | ホッカイドウ | サッポロシチュウオウク | キタ5ジョウニシ(1-24チョウメ) | 北海道 | 札幌市中央区 | 北五条西（１〜２４丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
| 01101 | 064 | 0640825 | ホッカイドウ | サッポロシチュウオウク | キタ5ジョウニシ(25-29チョウメ) | 北海道 | 札幌市中央区 | 北五条西（２５〜２９丁目） | 1 | 0 | 1 | 0 | 0 | 0 |
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
20 rows in set (0.17 sec)
下記情報を参考に、「MySQLのODBCドライバインストール＆設定」「CSA Data UploaderのODBC接続の作成」を行い、
CSA Data Uploaderのジョブとして「データアップロードジョブ」を作成、入力種別に「ODBC接続」を指定した上で対象データベースにクエリを実行する設定を行います。[読み込みテスト]押下でOKが出ればアクセス出来ています。[保存]を押下。
入力要素右の[データ表示]押下でデータが表示出来ていれば入力設定は上手く行っています。
変換処理の作成:項目の抽出と並び替え
ここでは、入力処理で取得出来ていた情報から「必要な項目のみ選択」し「選択した項目を並び替え」る処理を変換処理として定義します。
まずは「カラム選択」から。ジョブの変換要素作成ボタンを押下し、下記のような形で任意の項目の選択チェックを外します。選択した内容のみが次の処理対象の列となる流れです。
続いて「カラムの並び替え」。ジョブの変換要素作成ボタンを押下して処理を追加した上で、「変更後の並び順」で表示された並び順で後続処理へとデータを連携します。ここでは「zipcode_old」を一番後ろに配置換えする処理を定義しました。[保存]を押下。
これで2つの変換処理が入力データに対して定義されました。変換処理右の[データ表示]押下で、変換処理を経た項目が指定した並び順で絞り込み＆配置されている事が確認出来ます。
出力処理の作成:Amazon S3へアップロード
最後にアップロード処理の定義。ここでは予め任意のAWSアカウント配下のAmazon S3にアップロード可能なAWS CLIの設定を行っておき、
対象ジョブの出力要素作成で下記のような形で設定を行います。ここでは敢えて任意の固定ファイル名でアップロードする形を指定してみました。なお、CSA Data Upploaderのアップロード処理に於いては現状下記仕様は共通/固定のものとなっています。
- ファイルの文字コード：UTF-8固定
- ファイルの圧縮有無及び圧縮形式：圧縮する＆gzip圧縮固定
データアップロードジョブの実行＆内容確認
これで、今回実現したい処理の定義がすべて整いました。[保存して実行]で早速挙動を確認してみます。
実行タイプは「今すぐ実行」で試してみます。
実行がなされると画面上部には実行を開始した旨のメッセージが表示されます。
そして処理が完了すると画面右上に完了通知メッセージが表示されます。
実行結果は履歴として確認可能です。メニューの[参照]→[ジョブ実行履歴]に遷移し、対象ジョブの履歴表示アイコンを選択。
以下のように、実行結果をログとして確認出来ました。
また、合わせて対象Amazon S3のパスも確認してみます。指定した形式やファイル名でアップロードされている事が確認出来ました。
ファイルを一旦ダウンロードして中身も確認してみます。tsv形式で生成されている事が確認出来ました。
% gunzip ken-all-utf8.tsv.gz % wc ken-all-utf8.tsv 124509 795197 6990006 ken-all-utf8.tsv % vi ken-all-utf8.tsv jiscode zipcode pref city street zipcode_old 01102 0010000 北海道 札幌市北区 以下に掲載がない場合 001 01102 0010010 北海道 札幌市北区 北十条西（１〜４丁目） 001 01102 0010011 北海道 札幌市北区 北十一条西（１〜４丁目） 001 01102 0010012 北海道 札幌市北区 北十二条西（１〜４丁目） 001 01102 0010013 北海道 札幌市北区 北十三条西（１〜４丁目） 001 01102 0010014 北海道 札幌市北区 北十四条西（１〜４丁目） 001 01102 0010015 北海道 札幌市北区 北十五条西（１〜５丁目） 001 01102 0010016 北海道 札幌市北区 北十六条西（１〜６丁目） 001 01102 0010017 北海道 札幌市北区 北十七条西（１〜６丁目） 001 01102 0010018 北海道 札幌市北区 北十八条西（１〜７丁目） 001 01102 0010019 北海道 札幌市北区 北十九条西（２〜７丁目） 001 01102 0010020 北海道 札幌市北区 北二十条西（２〜１０丁目） 001 01102 0010021 北海道 札幌市北区 北二十一条西 001 01102 0010022 北海道 札幌市北区 北二十二条西 001 : :
まとめ
という訳で、『クラスメソッド CSアナリティクス Advent Calendar 2020』13本目のエントリ、CSA Data Uploaderで「データアップロードジョブ」(任意の入力/変換/出力を一連の処理として定義したもの)を作成・実行する手順の紹介でした。CSA Data Uploaderは1ヶ月間のトライアル利用が可能となっています。興味をお持ち頂いた方は是非無料版ダウンロードページからインストーラを入手頂き、お試し頂けますと幸いです。
では、明日(14日目)のエントリもお楽しみに！
